#!/bin/sh

if [ -n "$1" ]; then
    data_date=$1
else
  data_date=$(date -d "-1 days" +%F)
fi

LOAD_DATA_ADS="
insert overwrite table xm1_ads.ads_traffic_view_report
select dt,
       recent_days,
       province_name,
       city_name,
       page_view_count,
       user_count,
       session_count,
       avg_user_session_count,
       session_during_time,
       avg_session_during_time,
       bounce_count,
       bounce_count_rate
    from xm1_ads.ads_traffic_view_report
union
select
    '${data_date}' as dt,
    recent_days ,
    province_name,
    city_name,
    sum(if(dt>=date_sub('${data_date}',tmp.recent_days-1),session_pv,0)) as page_view_count,
    count(distinct if(dt>=date_sub('${data_date}',tmp.recent_days-1),deviceid,null)) as user_count,
    count(if(dt>=date_sub('${data_date}',tmp.recent_days-1),session_pv,null)) as session_count,
    sum(if(dt>=date_sub('${data_date}',tmp.recent_days-1),session_pv,0))
        /count(distinct if(dt>=date_sub('${data_date}',tmp.recent_days-1),deviceid,null)) as avg_user_session_count,
    sum(if(dt>=date_sub('${data_date}',tmp.recent_days-1),session_during_time,0)) as session_during_time,
    avg(if(dt>=date_sub('${data_date}',tmp.recent_days-1),session_during_time,0)) as avg_session_during_time,
    sum(if(if(dt>=date_sub('${data_date}',tmp.recent_days-1),session_pv,0)=1,1,0)) as bounce_count,
    sum(if(if(dt>=date_sub('${data_date}',tmp.recent_days-1),session_pv,0)=1,1,0))
        /count(if(dt>=date_sub('${data_date}',tmp.recent_days-1),session_pv,null)) as bounce_count_rate

from xm1_dws.dws_session_log
lateral view explode(array(1,3,7)) tmp as recent_days
where dt>=date_sub('${data_date}',6) and dt<='${data_date}' and province_name is not null
group by  province_name, city_name,recent_days;"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${LOAD_DATA_ADS}"


 /opt/module/sqoop/bin/sqoop export \
--connect "jdbc:mysql://node101:3306/xm1_lx?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 123456 \
--table traffic_view_report \
--export-dir /user/spark/warehouse/zg6_xm1_lx/ads \
--input-fields-terminated-by "\001" \
--update-mode allowinsert \
 --update-key "dt,recent_days,province,city" \
       --num-mappers 1 \
       --input-null-string '\\N' \
       --input-null-non-string '\\N'